query: SELECT DISTINCT DLR_QLFY.DLR_QLFY_ID as DLR_QLFY_ID, NMQ_REQ.GRACE_PRD as GRACE_PRD, NMQ_REQ.HIDE_PRG_FLG as HIDE_PRG_FLG, NMQ_REQ.NTFY_DLR_FLG as NTFY_DLR_FLG, DLR_LOC.ACCT_NUM as ACCT_NUM, NMQ_REQ.NMQ_REQ_ID as NMQ_REQ_ID, NEW_MDL.PI_MDL_ID as PI_MDL_ID FROM test.DLR_QLFY INNER JOIN (SELECT DLR_GRP.DLR_GRP_ID AS LOC_GRP_ID,LEAD_DLR_LOC_ID,DLR_LOC.ACCT_NUM AS LOC_ACCT_NUM FROM test.DLR_GRP, test.DLR_GRP_DLR_XREF, test.DLR_LOC WHERE DLR_GRP.DLR_GRP_ID=DLR_GRP_DLR_XREF.DLR_GRP_ID AND DLR_GRP_DLR_XREF.DLR_LOC_ID=DLR_LOC.DLR_LOC_ID AND (DLR_GRP.DLR_GRP_TYP='LOC' OR DLR_GRP.DLR_GRP_TYP='COG') AND DLR_LOC.IS_ACTV='Y' ) LOC_GRP ON DLR_QLFY.QLFY_GRP_ID=LOC_GRP.LOC_GRP_ID INNER JOIN (SELECT DLR_GRP.DLR_GRP_ID AS COG_GRP_ID,LEAD_DLR_LOC_ID,DLR_LOC.ACCT_NUM AS COG_ACCT_NUM FROM test.DLR_GRP,test.DLR_GRP_DLR_XREF,test.DLR_LOC WHERE DLR_GRP.DLR_GRP_ID=DLR_GRP_DLR_XREF.DLR_GRP_ID AND DLR_GRP_DLR_XREF.DLR_LOC_ID=DLR_LOC.DLR_LOC_ID AND DLR_GRP.DLR_GRP_TYP='COG' AND DLR_LOC.IS_ACTV='Y' ) COG_GRP ON DLR_QLFY.COG_GRP_ID=COG_GRP.COG_GRP_ID INNER JOIN test.DLR_LOC ON DLR_LOC.ACCT_NUM=LOC_GRP.LOC_ACCT_NUM AND DLR_LOC.ACCT_NUM=COG_GRP.COG_ACCT_NUM INNER JOIN test.DLR_LOC LEAD_LOC ON LEAD_LOC.DLR_LOC_ID=COG_GRP.LEAD_DLR_LOC_ID AND LEAD_LOC.ACCT_NUM=LEAD_LOC.COG_PARNT_ACCT INNER JOIN test.DLR_LOC COG_LEAD ON COG_LEAD.DLR_LOC_ID=COG_GRP.LEAD_DLR_LOC_ID INNER JOIN test.NMQ_REQ ON DLR_QLFY.NMQ_REQ_ID=NMQ_REQ.NMQ_REQ_ID INNER JOIN test.NEW_MDL ON NMQ_REQ.NEW_MDL_ID = NEW_MDL.NEW_MDL_ID INNER JOIN test.STG_ACFLX_NMQ_DLRS ON COG_LEAD.ACCT_NUM=STG_ACFLX_NMQ_DLRS.RLTNP_LEAD_ACCT AND STG_ACFLX_NMQ_DLRS.ACCT_ID=DLR_LOC.ACCT_NUM WHERE DLR_LOC.IS_ACTV='Y' AND DLR_QLFY.QLF_FLG='N' AND NMQ_REQ.PGM_DSBL_FLG != 'Y' AND (NMQ_REQ.PGM_START_DT <= CURRENT_DATE AND NMQ_REQ.PGM_END_DT > CURRENT_DATE) AND DLR_QLFY.DLR_QLFY_ID NOT IN (SELECT DLR_QLFY.DLR_QLFY_ID FROM test.DLR_QLFY WHERE QLF_FLG='Y' AND DLR_QLFY.NMQ_REQ_ID=NMQ_REQ.NMQ_REQ_ID); --------------------------------------------------------------------------- access plan "HashAggregate (cost=4538.33..4538.34 rows=1 width=27)" " Group Key: dlr_qlfy.dlr_qlfy_id, nmq_req.grace_prd, nmq_req.hide_prg_flg, nmq_req.ntfy_dlr_flg, dlr_loc.acct_num, nmq_req.nmq_req_id, new_mdl.pi_mdl_id" " -> Nested Loop (cost=3.59..4538.31 rows=1 width=27)" " -> Nested Loop (cost=3.31..4537.94 rows=1 width=27)" " -> Nested Loop (cost=3.03..4530.16 rows=1 width=15)" " Join Filter: (lead_loc.dlr_loc_id = dlr_grp_1.lead_dlr_loc_id)" " -> Nested Loop (cost=0.58..1438.27 rows=263 width=15)" " -> Nested Loop (cost=0.29..1306.78 rows=169 width=15)" " -> Seq Scan on dlr_loc lead_loc (cost=0.00..757.12 rows=169 width=4)" " Filter: (acct_num = cog_parnt_acct)" " -> Index Only Scan using "IDX_101" on dlr_loc cog_lead (cost=0.29..3.24 rows=1 width=11)" " Index Cond: (dlr_loc_id = lead_loc.dlr_loc_id)" " -> Index Scan using idx_14 on stg_acflx_nmq_dlrs (cost=0.29..0.63 rows=15 width=14)" " Index Cond: (rltnp_lead_acct = cog_lead.acct_num)" " -> Nested Loop (cost=2.45..11.74 rows=1 width=33)" " -> Index Only Scan using idx3 on dlr_grp dlr_grp_1 (cost=0.29..0.32 rows=1 width=8)" " Index Cond: ((lead_dlr_loc_id = cog_lead.dlr_loc_id) AND (dlr_grp_typ = 'COG'::bpchar))" " -> Nested Loop (cost=2.17..11.41 rows=1 width=37)" " Join Filter: (dlr_loc_2.acct_num = dlr_loc.acct_num)" " -> Nested Loop (cost=0.58..0.77 rows=1 width=11)" " -> Index Only Scan using idx6 on dlr_loc dlr_loc_2 (cost=0.29..0.32 rows=1 width=11)" " Index Cond: ((acct_num = stg_acflx_nmq_dlrs.acct_id) AND (is_actv = 'Y'::bpchar))" " -> Index Only Scan using idx7 on dlr_grp_dlr_xref dlr_grp_dlr_xref_1 (cost=0.29..0.44 rows=1 width=8)" " Index Cond: ((dlr_loc_id = dlr_loc_2.dlr_loc_id) AND (dlr_grp_id = dlr_grp_1.dlr_grp_id))" " -> Nested Loop (cost=1.58..10.63 rows=1 width=26)" " -> Index Only Scan using idx_102 on dlr_loc (cost=0.29..0.32 rows=1 width=7)" " Index Cond: ((acct_num = stg_acflx_nmq_dlrs.acct_id) AND (is_actv = 'Y'::bpchar))" " -> Nested Loop (cost=1.29..10.30 rows=1 width=19)" " -> Index Only Scan using idx6 on dlr_loc dlr_loc_1 (cost=0.29..0.34 rows=1 width=11)" " Index Cond: ((acct_num = dlr_loc.acct_num) AND (is_actv = 'Y'::bpchar))" " -> Nested Loop (cost=1.00..9.95 rows=1 width=16)" " -> Index Only Scan using idx7 on dlr_grp_dlr_xref (cost=0.29..0.35 rows=2 width=8)" " Index Cond: (dlr_loc_id = dlr_loc_1.dlr_loc_id)" " -> Nested Loop (cost=0.71..4.79 rows=1 width=20)" " -> Index Scan using idxdg3 on dlr_grp (cost=0.29..0.33 rows=1 width=4)" " Index Cond: (dlr_grp_id = dlr_grp_dlr_xref.dlr_grp_id)" " Filter: ((dlr_grp_typ = 'LOC'::bpchar) OR (dlr_grp_typ = 'COG'::bpchar))" " -> Index Only Scan using idxdq7 on dlr_qlfy (cost=0.43..4.45 rows=1 width=16)" " Index Cond: ((qlfy_grp_id = dlr_grp.dlr_grp_id) AND (qlf_flg = 'N'::bpchar) AND (cog_grp_id = dlr_grp_dlr_xref_1.dlr_grp_id))" " -> Index Scan using p_key_29 on nmq_req (cost=0.28..7.77 rows=1 width=16)" " Index Cond: (nmq_req_id = dlr_qlfy.nmq_req_id)" " Filter: ((pgm_dsbl_flg <> 'Y'::bpchar) AND (pgm_start_dt <= ('now'::cstring)::date) AND (pgm_end_dt > ('now'::cstring)::date) AND (NOT (SubPlan 1)))" " SubPlan 1" " -> Index Only Scan using idx11 on dlr_qlfy dlr_qlfy_1 (cost=0.43..13.81 rows=269 width=4)" " Index Cond: ((nmq_req_id = nmq_req.nmq_req_id) AND (qlf_flg = 'Y'::bpchar))" " -> Index Scan using idx1 on new_mdl (cost=0.28..0.37 rows=1 width=8)" " Index Cond: (new_mdl_id = nmq_req.new_mdl_id)"